-- @owner: ningyali
-- @date: 2024-08-12
-- @testpoint: 不消除子查询和distinct

--step1：创建测试表与索引; expect：成功
drop table if exists t_ustore_dml_orderby_0064_01 cascade ;
create table t_ustore_dml_orderby_0064_01(id int,deptno int,name varchar(20),sal int,col_16 int[],b_boolean boolean,f_floor decimal(10,2)) with (storage_type=ustore);
drop index if exists i_ustore_dml_orderby_0064_01 cascade;
create unique index i_ustore_dml_orderby_0064_01 on t_ustore_dml_orderby_0064_01(id);
drop table if exists t_ustore_dml_orderby_0064_02 cascade ;
create table t_ustore_dml_orderby_0064_02(
     col_1 integer,
     col_2 bigint constraint cons1_t_ustore_dml_orderby_0064_02 primary key,
     col_3 double precision,
     col_4 decimal(12,6),
     col_5 bool,
     col_6 char(30),
     col_7 varchar2(50),
     col_8 varchar(30),
     col_9 interval day to second,
     col_10 timestamp,
     col_11 date,
     col_12 date,
     col_13 timestamp without time zone,
     col_14 blob,
     col_15 clob,
     col_16 int[]
) with (storage_type=ustore);

--step2：插入数据; expect：成功
insert into t_ustore_dml_orderby_0064_01 values(1,1,'1aa',120,'{1,2,3}',true,10.2);
insert into t_ustore_dml_orderby_0064_01 values(2,1,'2aa',300,'{1,3,3}',false,100.2);
insert into t_ustore_dml_orderby_0064_01 values(3,1,'3aa',100,'{1,2,3}',true,99.2);
insert into t_ustore_dml_orderby_0064_01 values(4,1,'4aa',99,'{1,2,3}',true,67.2);
insert into t_ustore_dml_orderby_0064_01 values(5,1,'5aa',90,'{1,3,3}',false,67.2);
insert into t_ustore_dml_orderby_0064_01 values(6,2,'6aa',87,'{1,2,3}',true,10.2);
insert into t_ustore_dml_orderby_0064_01 values(7,2,'7aa',500,'{1,2,3}',false,99.2);
insert into t_ustore_dml_orderby_0064_01 values(8,2,'8aa',200,'{2,3,3}',true,10.2);
insert into t_ustore_dml_orderby_0064_01 values(9,2,'9aa',20,'{1,3,3}',false,99.5);
insert into t_ustore_dml_orderby_0064_01 values(10,2,'10aa',30,'{2,3,3}',true,99.5);
insert into t_ustore_dml_orderby_0064_01 values(null,2,'10aa',30,'{2,3,3}',false,10.2);
insert into t_ustore_dml_orderby_0064_01 values(12,2,'10aa',null,'{2,3,3}',true,10.2);
begin
for i in 1 .. 20 loop
insert into t_ustore_dml_orderby_0064_01 (deptno,sal,col_16,b_boolean,f_floor)values(1,120,'{1,2,3}',true,10.2);
end loop;
end;
/
drop sequence if exists seq_ustore_dml_orderby_0064_02 cascade;
create sequence seq_ustore_dml_orderby_0064_02 increment by 1 start with 10;
truncate table t_ustore_dml_orderby_0064_02;
insert into t_ustore_dml_orderby_0064_02 values(1,seq_ustore_dml_orderby_0064_02.nextval,   1+445.255,98*0.99,  true,lpad('abc','6','@'),lpad('abc','5','b'),rpad('abc','6','e'),(INTERVAL '4 5:12:10.222' DAY TO SECOND(3)),to_timestamp('2019-01-03 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FFFFFF'),    TO_DATE('2018-11-03 14:14:12'),TO_DATE('2019-01-03 14:14:12'),TO_DATE('2019-01-03 14:14:12'),'1010101111111111111',rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_dml_orderby_0064_02 values(1,seq_ustore_dml_orderby_0064_02.nextval, 1+445.255,98*0.99,  true,lpad('abc','6','@'),lpad('abc','6','b'),rpad('abc','5','e'),(INTERVAL '4 5:12:10.222' DAY TO SECOND(3)),to_timestamp('2019-01-03 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FFFFFF'),    TO_DATE('2018-11-03 14:14:12'),TO_DATE('2019-01-03 14:14:12'),TO_DATE('2019-01-03 14:14:12'),'1010101111111111111',rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_dml_orderby_0064_02 values(2,seq_ustore_dml_orderby_0064_02.nextval,   1+445.255,98*0.99, false,lpad('abc','6','@'),lpad('abc','5','b'),rpad('abc','5','e'),(INTERVAL '4 5:12:10.222' DAY TO SECOND(3)),to_timestamp('2019-01-03 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FFFFFF'),    TO_DATE('2018-11-03 14:14:12'),TO_DATE('2019-01-03 14:14:12'),TO_DATE('2019-01-03 14:14:12'),'1010101111111111111',rpad('abc','9','a@123&^%djgk'),'{43,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_dml_orderby_0064_02 values(3,seq_ustore_dml_orderby_0064_02.nextval,   1+445.255,98*0.99, false,lpad('abc','4','@'),lpad('abc','4','b'),rpad('abc','6','e'),(INTERVAL '4 5:12:10.222' DAY TO SECOND(3)),to_timestamp('2019-01-03 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FFFFFF'),    TO_DATE('2018-11-03 14:14:12'),TO_DATE('2019-01-03 14:14:12'),TO_DATE('2019-01-03 14:14:12'),'1010101111111111111',rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_dml_orderby_0064_02 values(3,seq_ustore_dml_orderby_0064_02.nextval,   1+445.255,98*0.99, false,lpad('abc','3','@'),lpad('abc','3','b'),rpad('abc','3','e'),(INTERVAL '4 5:07:10.222' DAY TO SECOND(3)),to_timestamp('2019-01-03 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FFFFFF'),    TO_DATE('2018-11-03 14:14:12'),TO_DATE('2019-01-03 14:14:12'),TO_DATE('2019-01-03 14:14:12'),'1010101111111111111',rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_dml_orderby_0064_02 values(4,seq_ustore_dml_orderby_0064_02.nextval,   1+445.255,98*0.99,  true,lpad('abc','3','@'),lpad('abc','4','b'),rpad('abc','6','e'),(INTERVAL '4 5:07:10.222' DAY TO SECOND(3)),to_timestamp('2019-01-03 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FFFFFF'),    TO_DATE('2018-11-03 14:14:12'),TO_DATE('2019-01-03 14:14:12'),TO_DATE('2019-01-03 14:14:12'),'1010101111111111111',rpad('abc','9','a@123&^%djgk'),'{43,535,5645645,6767,76,67,56,48,979,978,7}');
begin
for i in 6 .. 20 loop
insert into t_ustore_dml_orderby_0064_02 values(i,'1'||i,446.255,98*0.99,true,lpad('abc','6','@'),lpad('abc','5','b'),rpad('abc','6','e'),(INTERVAL '4 5:12:10.222' DAY TO SECOND(3)),to_timestamp('2019-01-03 14:58:54.000000','YYYY-MM-DD HH24:MI:SS.FFFFFF'),    TO_DATE('2018-11-03 14:14:12'),TO_DATE('2019-01-03 14:14:12'),TO_DATE('2019-01-03 14:14:12'),'1010101111111111111',rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');
end loop;
end;
/

--step3：不消除子查询和distinct; expect：成功
select t0.id from (select distinct (case when id<10 then id+1 when id<15 then id+2 else id+3 end) id from t_ustore_dml_orderby_0064_01 where id <20) t0 order by t0.id;
select t0.id from (select distinct (case when id<10 then id+1 when id<15 then id+2 else id+3 end) id from t_ustore_dml_orderby_0064_01 where id <20)t0 left join (select distinct col_1,col_2,col_3 from t_ustore_dml_orderby_0064_02 where col_1 < 30)t1 on t0.id = t1.col_1 order by t0.id;

--step4：清理环境; except：成功
drop index if exists i_ustore_dml_orderby_0064_01;
drop sequence seq_ustore_dml_orderby_0064_02;
drop table if exists t_ustore_dml_orderby_0064_01 cascade;
drop table if exists t_ustore_dml_orderby_0064_02 cascade;